﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Reflection;
using System.Data.Linq;
using System.Diagnostics;
using System.Threading.Tasks;
using System.Threading;
using System.Xml.Linq;

namespace gb_racu_lastmessage
{
    class Program
    {
        private static string connString = "Data Source=192.168.1.9;Initial Catalog=sz_23B_20100920;Persist Security Info=True;User ID=weihp;Password=admin123456";
        //ConfigurationSettings.AppSettings["sz_23B_RACU20100920ConnectionString"];
        //sz_46C_20101012
        //sz_23B_RACU20100920
        //sz_23B_20100920

        private static DataClasses1DataContext mydb = new DataClasses1DataContext(connString);

        static void Main(string[] args)
        {
            XElement dataConfig = XElement.Load(@"G:\signallingmining\gb_racu_lastmessage\bin\Debug\Filters.xml");
            XElement mod = dataConfig.Elements("FilterStrings").ElementAt(0);
            string mess = mod.Element("FilterString").Value;
            Console.WriteLine(mess);
            Console.WriteLine(connString);
            Console.Write("是否初始化数据库？");
            string inittable = Console.ReadLine();
            if (inittable == "yes")
            {
                Console.WriteLine(inittable);
                initTable();
            }
            Console.Write("是否给原始数据加主键？");
            string pk = Console.ReadLine();
            if (pk == "yes")
            {
                Console.WriteLine(AlterPrimaryKey());
            }

            var message = mydb._LastFirstMessage.Select(e => e.ThisMessageFileNum).Distinct().OrderBy(e => e.Value);
            foreach (var p in message)
                Console.Write(p.Value + ",");
            Console.Write("输入文件编号：");
            int filnum = int.Parse(Console.ReadLine());
            Console.WriteLine(filnum);
            SendOrders(mess, filnum);
            //for (int i = 0; i < 5; i++)
            //{
            //    SendOrders(mess, i);
            //    Thread.Sleep(1000);
            //}

            //Parallel.For(0, 3, i => {SendOrders(mess,i); });
            Console.ReadKey();
        }

        private static IEnumerable<_LastFirstMessage> GetLastFirstMessage(string mess, int filenum)
        {
            var message = mydb._Gb_ns_traffic.Where(e => e.FileNum == filenum).OrderBy(e => e.PacketNum);
            var messages = from p in message
                           select new { p.tlli, p.FileNum, p.PacketNum, p.ns_traffic_MsgType };
            var racu = messages.Where(e => e.ns_traffic_MsgType == mess).OrderBy(e => e.PacketNum);
            var racuDic = messages.ToLookup(e => e.tlli);
            foreach (var p in racu)
            {
                var last = racuDic[p.tlli].Where(e => e.PacketNum < p.PacketNum).OrderByDescending(e => e.PacketNum).FirstOrDefault();
                //var last = messages.Take(p.PacketNum.Value - 1).Where(e => e.tlli == p.tlli).OrderByDescending(e => e.PacketNum).FirstOrDefault();
                if (last != null)
                {
                    _LastFirstMessage lf = new _LastFirstMessage();
                    lf.ThisMessageFileNum = p.FileNum;
                    lf.ThisMessagePacketNum = p.PacketNum;
                    lf.LastFirstMessageFileNum = last.FileNum;
                    lf.LastFirstMessagePacketNum = last.PacketNum;
                    lf.LastFirstMessageMsgType = last.ns_traffic_MsgType;
                    lf.TLLI = p.tlli;
                    Console.WriteLine(lf.LastFirstMessageMsgType);
                    yield return lf;
                }
            }
        }

        private static void SendOrders(string mess, int filenum)
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                using (SqlTransaction tran = con.BeginTransaction())
                {
                    var newOrders = GetLastFirstMessage(mess, filenum);
                    SqlBulkCopy bc = new SqlBulkCopy(con,
                      SqlBulkCopyOptions.CheckConstraints |
                      SqlBulkCopyOptions.FireTriggers |
                      SqlBulkCopyOptions.KeepNulls, tran);
                    bc.BatchSize = 1000;
                    bc.DestinationTableName = "_LastFirstMessage";
                    bc.WriteToServer(newOrders.AsDataReader());
                    tran.Commit();
                }
                con.Close();
            }
            Console.WriteLine(filenum.ToString() + "---" + sw.Elapsed.TotalSeconds.ToString() + "---");
            GC.Collect();
            GC.Collect();
            GC.Collect();
        }

        private static void initTable()
        {
            var typeName = "System.Data.Linq.SqlClient.SqlBuilder";
            var type = typeof(DataContext).Assembly.GetType(typeName);
            var bf = BindingFlags.Static | BindingFlags.NonPublic | BindingFlags.InvokeMethod;
            var metaTable = mydb.Mapping.GetTable(typeof(_LastFirstMessage));
            var sql = type.InvokeMember("GetCreateTableCommand", bf, null, null, new[] { metaTable });
            string delSql = @"if exists (select 1 from  sysobjects where  id = object_id('dbo._LastFirstMessage') and   type = 'U')
                            drop table dbo._LastFirstMessage";
            mydb.ExecuteCommand(delSql.ToString());
            mydb.ExecuteCommand(sql.ToString());
        }

        private static string AlterPrimaryKey()
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();
            mydb.CommandTimeout = 0;//sql连接超时的问题
            mydb.ExecuteCommand("alter table _Gb_ns_traffic alter column FileNum int not null");
            mydb.ExecuteCommand("alter table _Gb_ns_traffic alter column PacketNum int not null");
            mydb.ExecuteCommand("alter table _Gb_ns_traffic add constraint sid_pk primary key(FileNum,PacketNum)");
            sw.Stop();
            return sw.Elapsed.TotalSeconds.ToString();
        }
    }
}
